USE [SSB]
GO

CREATE PROCEDURE [SSBPubSub].[usp_NewPubRequirement]
		@Subject varchar(50)
		,@FromService varchar(100)
		,@ToService varchar(100)
        ,@Action char(8)
        ,@Server varchar(30)
AS

BEGIN
	SET NOCOUNT ON;
	DECLARE @dh uniqueidentifier;
	DECLARE @Error_Description varchar(2000);
  SET @dh=NEWID()
	DECLARE @msg xml
	SET @msg=N'<?xml version="1.0"?>
		<Publish xmlns="http://ssbrouter/v10/SSBPubSubService">
			<Subject>'+@Subject+'</Subject>
			 <Server>'+@Server+'</Server>
			<FromService>'+@FromService+'</FromService>
			<ToService>'+@ToService+'</ToService>
			<Action>'+@Action+'</Action>
		</Publish>'
	BEGIN TRY
		BEGIN dialog CONVERSATION @dh
			FROM SERVICE @FromService
			TO SERVICE @ToService
			ON CONTRACT [http://ssbrouter/v10/SSBPublishContract]
			WITH encryption = OFF;
		SEND ON CONVERSATION @dh
			MESSAGE TYPE [http://ssbrouter/v10/Types/SSBPublishMsg]
			(@msg); 
		INSERT INTO [SSBPubSub].[Publications_Record]([Conversation_handle] ,
			[Subject] ,
			FromService,
			ToService,
			[Server] ,
			[Action],
			Message_body ,
			InDate ) 
		VALUES(@dh,@Subject,@FromService,@ToService,@Server,@Action,@msg,Getdate());
	END TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@msg,getdate(),@dh,@Error_Description);
			END
	END CATCH
END

GO


CREATE PROCEDURE [SSBPubSub].[usp_NewSubRequirement]
		@Subject varchar(50)
		,@FromService varchar(100)
		,@ToService varchar(100)
        ,@Action char(8)
        ,@Server varchar(30)
AS
BEGIN
	DECLARE @dh uniqueidentifier;
	DECLARE @msg xml
	DECLARE @Error_Description Varchar(2000);
	SET @dh=newid()
	SET @msg=N'<?xml version="1.0"?>
		    <Request xmlns="http://ssbrouter/v10/SSBPubSubService">
			<Subject>'+@Subject+'</Subject>
		    <Server>'+@Server+'</Server>
			<FromService>'+@FromService+'</FromService>
			<ToService>'+@ToService+'</ToService>
			<Action>'+@Action+'</Action>
		</Request>'
	BEGIN TRY
		BEGIN dialog CONVERSATION @dh
			FROM SERVICE @ToService
			TO SERVICE @FromService
			ON CONTRACT [http://ssbrouter/v10/SSBSubscribeContract]
			WITH encryption = OFF;
		SEND ON CONVERSATION @dh
			MESSAGE TYPE [http://ssbrouter/v10/Types/SSBSubscribeMsg]
			(@msg); 
		INSERT INTO [SSBPubSub].[Subscriptions_Record]([Conversation_handle] ,
			[Subject] ,
			FromService,
			ToService,
			[Server] ,
			[Action],
			Message_body ,
			InDate ) 
		VALUES(@dh,@Subject,@FromService,@ToService,@Server,@Action,@msg,Getdate());
	END TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@msg,getdate(),@dh,@Error_Description);
			END
	END CATCH
END

GO



CREATE procedure [SSBPubSub].[usp_SendArticle]
	@RequestMSG xml
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @dh uniqueidentifier;
	DECLARE @Subject varchar(50)
            ,@FromService varchar(100)
			,@ToService varchar(100)
            ,@Action char(8)
			,@Counter int
			,@error int
			,@Error_Description varchar(2000);
	SET @Counter=1;
	
	 
	WITH XMLNAMESPACES (DEFAULT 'http://ssbrouter/v10/SSBPubSubService') 
		SELECT @FromService=@RequestMSG.value(N'(/Publish/FromService)[1]', N'varchar(100)')
            ,@ToService=@RequestMSG.value(N'(/Publish/ToService)[1]', N'varchar(100)');
        BEGIN TRY
           
 
			BEGIN DIALOG CONVERSATION @dh
					FROM SERVICE @FromService
					TO SERVICE @ToService
					ON CONTRACT [http://ssbrouter/v10/SSBPublishContract]
					WITH encryption = OFF;

			 SEND ON CONVERSATION @dh
					MESSAGE TYPE [http://ssbrouter/v10/Types/SSBArticleMsg]
					(@RequestMSG); 
		   SELECT @error = @@ERROR;
            IF @error = 0
            BEGIN-- Successful send, just exit the loop
			   INSERT [SSBPubSub].[Send_Record](Message_body,InDate,[Conversation_handle])
               VALUES( @RequestMSG,getdate(),@dh)
               
            END
			     
      END   TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@RequestMSG,getdate(),@dh,@Error_Description);
 
			END
	END CATCH;
 
 
END

GO